from lxml import etree
import requests
import Sha1Util
import 录入数据库方法
headers = {
            "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) "
                          "AppleWebKit/537.36 (KHTML, like Gecko) Chrome/81.0.4044.138 Safari/537.36"

        }

def city():
    url = "https://www.jianzhimao.com/ctrlcity/changeCity.html"
    page=requests.get(url=url,headers=headers)
    page_text=page.text
    tree=etree.HTML(page_text)
    title_list_name = tree.xpath('//ul[@class="city_table"]//a[@target="_blank"]/text()')
    title_list_url = tree.xpath('//ul[@class="city_table"]//a/@href')
    dict_data = dict(zip(title_list_name,title_list_url))

    # 创建表结构
    createTable()
    # 新增数据
    addHomeData(dict_data)

def createTable():
    '''
    建表
    :return:
    '''
    sql = '''
    create table if not exists 兼职猫城市(
        id varchar(50) not null primary key,
        caty varchar(20) not null,
        url varchar(150) not null )
    '''
    录入数据库方法.createTable(sql=sql)

def addHomeData(dict_data):

    for title_list_name,title_list_url in dict_data.items():
        id = Sha1Util.jia_mi(title_list_name + title_list_url)
        sql = ''' 
        insert into 兼职猫城市(id,caty,url)
        values('%s','%s','%s') 
        ''' %(id,title_list_name,title_list_url)
        录入数据库方法.modifyTable(sql)
